package dto;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.User;

public class DtoUser {
	public static List<User> getAllUser(DataManager dataManager) {
		List<User> users = new ArrayList<User>();
		Connection conn = null;
		Statement statement = null;

		try {
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			String sqlQuery = "select * from User";
			
			ResultSet rs = statement.executeQuery(sqlQuery);
			
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt(1));
				user.setRoleId(rs.getInt(2));
				user.setUsername(rs.getString(3));
				user.setPassword(rs.getString(4));
				user.setEmail(rs.getString(5));
				user.setBrithDay(rs.getString(6));
				user.setDatetimeRegister(rs.getString(7));
				user.setOwnMoney((int) rs.getLong(8));
				
				users.add(user);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}

		return users;
	}
	
	public static User getUserWithUserID(DataManager dataManager, int userId){
		Connection conn = null;
		Statement statement = null;
		User user = null;
		String sqlQuery = "select * from user where userId = "+userId;
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlQuery);
			while(rs.next()){
				user = new User();
				user.setUserId(rs.getInt(1));
				user.setRoleId(rs.getInt(2));
				user.setUsername(rs.getString(3));
				user.setPassword(rs.getString(4));
				user.setEmail(rs.getString(5));
				user.setBrithDay(rs.getString(6));
				user.setDatetimeRegister(rs.getString(7));
				user.setOwnMoney(rs.getInt(8));
			}
		}
		catch (Exception e) {
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		return user;	
	}
	
	public static User getUserWithUserName(DataManager dataManager, String userName){
		Connection conn = null;
		Statement statement = null;
		User user = null;
		String sqlQuery = "select * from user where username = '"+userName+"'";
		//System.out.print(sqlQuery);
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlQuery);
			while(rs.next()){
				user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setRoleId(rs.getInt("roleId"));
				user.setUsername(rs.getString(3));
				user.setPassword(rs.getString(4));
				user.setEmail(rs.getString(5));
				user.setBrithDay(rs.getString(6));
				user.setDatetimeRegister(rs.getString(7));
				user.setOwnMoney(rs.getInt(8));
			}
		}
		catch (Exception e) {
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				//sqle.printStackTrace();
			}
		}
		return user;	
	}
	
	public static boolean insertUser(DataManager dataManager, User user) {
		Connection conn = null;
		Statement statement = null;
		String sqlQuery = "insert into user(roleId,username,password,email,birthday,datetimeRegister,ownMoney) " +
				"values("+user.getRoleId()+",'"+user.getUsername()+"','"+user.getPassword()+"','"+user.getEmail()+"','"+user.getBrithDay()+"','"+user.getDatetimeRegister()+"',"+user.getOwnMoney()+")";
		System.out.print(sqlQuery);
		boolean isOk = true;
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(sqlQuery);
		}
		catch (Exception e) {
			System.out.print(e.toString());
			isOk = false;
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		return isOk;
	}
	public static boolean deleteUser(DataManager dataManager, int userId) {
		Connection conn = null;
		Statement statement = null;
		String sqlQuery = "delete from user where userid="+userId;
		System.out.print(sqlQuery);
		boolean isOk = true;
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(sqlQuery);
		}
		catch (Exception e) {
			isOk = false;
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		return isOk;
	}
	
	
	public static boolean updateMoney(DataManager dataManager, int userId, int money) {
		Connection conn = null;
		Statement statement = null;
		String sqlQuery = "update User set ownMoney = "+money+" where userId = " + userId;
		//System.out.print(sqlQuery);
		boolean isOk = true;
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(sqlQuery);
		}
		catch (Exception e) {
			isOk = false;
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		return isOk;
	}
	
	public static boolean changePass(DataManager dataManager, int userId, String password)
	{
		Connection conn = null;
		Statement statement = null;
		String sqlQuery = "update User set password = '"+password+"' where userId = " + userId;
		//System.out.print(sqlQuery);
		boolean isOk = true;
		try{
			conn = dataManager.getConnection();
			statement = conn.createStatement();
			statement.executeUpdate(sqlQuery);
		}
		catch (Exception e) {
			isOk = false;
		}
		finally {
			try {
				dataManager.closeConnection(conn);
				if(statement != null)
					statement.close();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		return isOk;
		
	}
	
	public static boolean isUser(String username, String password, DataManager dataManager)
	{
		boolean check  = false; 
		List<User> users = getAllUser(dataManager);
		for(User u : users)
		{
			if(u.getUsername().equals(username)&& u.getPassword().equals(password))
			{
				check = true;
				break;
			}
		}
		return check ;
	}
}
